package dbHelper;

import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.lang.reflect.Type;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Locale;
import java.util.Map.Entry;
import java.util.Properties;
import java.util.TreeMap;

import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

import entities.Client;

public class DBHelper {

	private static Connection getConnection() throws SQLException {
		Locale.setDefault(Locale.ENGLISH);
		try {
			InitialContext ic = new InitialContext();
			DataSource ds = (DataSource) ic.lookup("java:jboss/datasources/NC");
			return ds.getConnection();
		} catch (NamingException e) {
			e.printStackTrace();
		}
		return null;
	}

	private static void CloseConnection(Connection Con) throws SQLException {

		Con.close();
	}

	public static ArrayList<Integer> getObjectsIDbyObjectTypeID(int ObjectTypeID)
			throws SQLException {
		ArrayList<Integer> Res = new ArrayList<Integer>();
		Connection Con = getConnection();
		PreparedStatement PS = Con
				.prepareStatement("SELECT OBJECT_ID FROM NC_OBJECTS WHERE OBJECT_TYPE_ID = ?");
		PS.setInt(1, ObjectTypeID);
		ResultSet RS = PS.executeQuery();
		while (RS.next()) {
			Res.add(RS.getInt(1));
		}
		RS.close();
		PS.close();
		CloseConnection(Con);
		return Res;
	}

	public static Object getFilledObject(Object Type, int ObjectID)
			throws IllegalAccessException, IllegalArgumentException,
			InvocationTargetException, SQLException, InstantiationException, NoSuchMethodException, SecurityException {
		Class Class = Type.getClass();
		Object Obj = Class.newInstance();
		Connection Con = getConnection();
		PreparedStatement PS = Con
				.prepareStatement("SELECT VALUE,ATTR_ID FROM NC_PARAMS WHERE OBJECT_ID = ?");
		PS.setInt(1, ObjectID);
		ResultSet RS = PS.executeQuery();
		while (RS.next()) {
			Object Value = RS.getObject(1);
			int Attr_id = RS.getInt(2);
			Reflector.getMethodbyAttrID(Type, Attr_id, "set")
					.invoke(Obj, Value);
		}
		Reflector.getMethodbyName(Type, "setId",int.class).invoke(Obj, ObjectID);

	    PS = Con
				.prepareStatement("SELECT NAME FROM NC_OBJECTS WHERE OBJECT_ID = ?");
		PS.setInt(1, ObjectID);
		RS = PS.executeQuery();
		while(RS.next())
		{
			String Name = RS.getString(1);
			Reflector.getMethodbyName(Type, "setName",String.class).invoke(Obj, Name);
		}
		PS.clearParameters();
		PS = Con
				.prepareStatement("select object_type_id,object_id from nc_objects where object_id  in ( SELECT OBJECT_REF_ID FROM NC_REFERENCES WHERE OBJECT_ID = ?)");
		PS.setInt(1, ObjectID);
		RS = PS.executeQuery();
		TreeMap<Integer, Class> Map = Reflector.getMapOfObjectTypesID();
		TreeMap<Integer, Method> MapRef = Reflector.getFieldMapOfObject(Obj,"set", true);
		while(RS.next())
		{
			int TypeID = RS.getInt(1);
			int ObjID = RS.getInt(2);
			Class Cl =Map.ceilingEntry(TypeID).getValue();
			Object O = Cl.newInstance();
			Object FillRefObj =  getFilledObject(O, ObjID);
			PreparedStatement PS1 = Con
					.prepareStatement("SELECT attr_id FROM NC_REFERENCES WHERE OBJECT_ID = ? and Object_Ref_ID =?");
			PS1.setInt(1, ObjectID);
			PS1.setInt(2, ObjID);
			ResultSet RS1 = PS1.executeQuery();
			int attrid=0;
			while(RS1.next())
			{
				attrid =  RS1.getInt(1);
			}
			RS1.close();
			PS1.close();
			
			MapRef.ceilingEntry(attrid).getValue().invoke(Obj, FillRefObj);
		}
		PS.close();
		RS.close();
		CloseConnection(Con);
		return Obj;
	}

	public static void addObject(Object Obj) throws SQLException,
			NoSuchMethodException, IllegalAccessException,
			IllegalArgumentException, InvocationTargetException {
		Connection Con = getConnection();
		PreparedStatement PS = Con
				.prepareStatement("INSERT INTO NC_OBJECTS (NAME,OBJECT_TYPE_ID) VALUES (?,?)");
		PS.setObject(1, Reflector.getParentName(Obj).invoke(Obj, null));
		PS.setInt(2, Reflector.GetObjectTypeID(Obj));
		PS.executeUpdate();
		PS.close();

		Statement st = Con.createStatement();
		ResultSet RS = st.executeQuery("Select max(OBJECT_ID) from NC_OBJECTS");
		int newID = 0;
		while (RS.next()) {
			newID = RS.getInt(1);
		}
		TreeMap<Integer, Method> Map = Reflector
				.getFieldMapOfObject(Obj, "get",false);
		PreparedStatement PS1 = Con
				.prepareStatement("INSERT INTO NC_PARAMS (VALUE,OBJECT_ID,ATTR_ID) VALUES (?,?,?)");
		while (!Map.isEmpty()) {
			Entry<Integer, Method> En = Map.pollFirstEntry();
			PS1.setObject(1, En.getValue().invoke(Obj, null));
			PS1.setInt(2, newID);
			PS1.setInt(3, En.getKey());
			PS1.addBatch();
		}
		PS1.executeBatch();
		PS1.close();
		 Map = Reflector
				.getFieldMapOfObject(Obj, "get",true);
		PreparedStatement PS2 = Con
				.prepareStatement("INSERT INTO NC_REFERENCES (ATTR_ID,OBJECT_ID,OBJECT_REF_ID) VALUES (?,?,?)");
		while (!Map.isEmpty()) {
			Entry<Integer, Method> En = Map.pollFirstEntry();
			Object refObj = En.getValue().invoke(Obj, null);
			PS2.setObject(1,En.getKey() );
			PS2.setInt(2, newID);
			Object refID = new Object();
			try
			{
				refID =  Reflector.getMethodbyName(refObj, "getId",null).invoke(refObj, null);
			}
			catch(NullPointerException e)
			{
				refID = null;
			}
			PS2.setObject(3, refID);
			PS2.addBatch();
		}
		PS2.executeBatch();
		PS2.close();
		
		
		CloseConnection(Con);
	}

	public static void updateObject(Object Obj) throws SQLException,
			NoSuchMethodException, IllegalAccessException,
			IllegalArgumentException, InvocationTargetException {
		int ID = (int) Reflector.getMethodbyName(Obj, "getId",null).invoke(Obj, null);
		Connection Con = getConnection();
		PreparedStatement PS = Con
				.prepareStatement("UPDATE NC_OBJECTS SET NAME = ? WHERE OBJECT_ID = ?");
		PS.setObject(1, Reflector.getParentName(Obj).invoke(Obj, null));
		PS.setInt(2,ID);
		PS.executeUpdate();
		PS.close();

		TreeMap<Integer, Method> Map = Reflector
				.getFieldMapOfObject(Obj, "get",false);
		PreparedStatement PS1 = Con.prepareStatement("UPDATE NC_PARAMS SET value =?  WHERE  ATTR_ID= ? and OBJECT_ID = ?");
		while (!Map.isEmpty()) {
			Entry<Integer, Method> En = Map.pollFirstEntry();
			Object O= En.getValue().invoke(Obj, null);
			int key = En.getKey();
			PS1.setObject(1, O);
			PS1.setInt(2, key);
			PS1.setInt(3, ID);
			PS1.addBatch();
		}
		PS1.executeBatch();
		PS1.close();
		
		 Map = Reflector
				.getFieldMapOfObject(Obj, "get",true);
		 PreparedStatement PS2 = Con
					.prepareStatement("UPDATE NC_REFERENCES SET OBJECT_REF_ID= ? WHERE  ATTR_ID= ? and OBJECT_ID = ? ");
			while (!Map.isEmpty()) {
				Entry<Integer, Method> En = Map.pollFirstEntry();
				Object refObj = En.getValue().invoke(Obj, null);
				PS2.setInt(1,(int) Reflector.getMethodbyName(refObj, "getId",null).invoke(refObj, null) );
				PS2.setInt(2, En.getKey());
				PS2.setInt(3, ID);
				PS2.addBatch();
			}
			PS2.executeBatch();
			PS2.close();
		CloseConnection(Con);
	}
	
	public static void deleteObject(Object Obj) throws IllegalAccessException, IllegalArgumentException, InvocationTargetException, NoSuchMethodException, SecurityException, SQLException
	{
		int ID = (int) Reflector.getMethodbyName(Obj, "getId",null).invoke(Obj, null);
		Connection Con = getConnection();
		PreparedStatement PS = Con
				.prepareStatement("DELETE FROM NC_PARAMS WHERE OBJECT_ID = ?");
		PS.setInt(1, ID);
		PS.executeUpdate();
		PS = Con
				.prepareStatement("DELETE FROM NC_REFERENCES WHERE OBJECT_ID = ?");
		PS.setInt(1, ID);
		PS.executeUpdate();
		PS = Con.prepareStatement("UPDATE nc_references set object_ref_id = null where ref_id in(select ref_id from nc_references where object_ref_id = ?)");
		PS.setInt(1, ID);
		PS.executeUpdate();
		PS = Con.prepareStatement("DELETE FROM NC_OBJECTS WHERE OBJECT_ID =?");
		PS.setInt(1, ID);
		PS.executeUpdate();
		PS.close();
		CloseConnection(Con);
	}

	public static ArrayList<?> findObjects(Object Obj) throws SQLException,
			IllegalAccessException, IllegalArgumentException,
			InvocationTargetException, InstantiationException, NoSuchMethodException, SecurityException {

		ArrayList<Object> Result = new ArrayList<Object>();
		int ObjectTypeID = Reflector.GetObjectTypeID(Obj);
		ArrayList<Integer> ObjectsID = getObjectsIDbyObjectTypeID(ObjectTypeID);
		for (int i = 0; i < ObjectsID.size(); i++) {
			Result.add(getFilledObject(Obj, ObjectsID.get(i)));
		}
		return Result;
	}
	
	private static int getAttrID( int ObjID, int ObjRefID) throws SQLException
	{
		Connection Con = getConnection();
		PreparedStatement PS = Con
				.prepareStatement("SELECT attr_id FROM NC_REFERENCES WHERE OBJECT_ID = ? and Object_Ref_ID =?");
		PS.setInt(1, ObjID);
		PS.setInt(2, ObjRefID);
		ResultSet RS = PS.executeQuery();
		int attrid=0;
		while(!RS.next())
		{
			attrid =  RS.getInt(1);
		}
		RS.close();
		PS.close();
		CloseConnection(Con);
		return attrid;
	}
	
	public static ResultSet executeQuery(String query) throws SQLException {
		Connection Con = getConnection();
		PreparedStatement PS = Con.prepareStatement(query);
		return  PS.executeQuery();
	}

}